主键,不少人以为自己懂了,却不透彻...
详见《InnoDB聚集索引,普通索引的索引差异》。
什么是回表,什么是索引覆盖,如何避免回表?
详见《如何避免回表查询?什么是索引覆盖?》。
主键太长为啥特别影响性能?
详见《数据库,主键为何不宜太长长长?》。
参考上述最佳实践,业务上经常采用这样的一些列作为主键:
用户ID:uid
消息ID:msgid
订单ID:oid
…
(1) create table user(
name varchar(10)
)engine=innodb;
(2) insert into user values('shenjian');
(3) insert into user values('shenjian');
(1) create table user(
id int,
name varchar(10),
primary key(id)
)engine=innodb;
(2) insert into user(name) values('shenjian');
(3) insert into user(name) values('shenjian');
(1) create table user(
id int not null,
name varchar(10) not null,
primary key(id, name)
)engine=innodb;
(2) insert into user values(1, 'shenjian');
(3) insert into user values(1, 'zhangsan');
(4) insert into user values(2, 'shenjian');
(1) create table user(
id int auto_increment,
name varchar(10) not null,
primary key(id)
)engine=innodb;
(2) insert into user(name) values('shenjian');
(3) insert into user(id, name) values(10,'shenjian');
(4) insert into user(name) values('shenjian');
(1) create table user(
id int auto_increment,
name varchar(10) not null,
primary key(name, id)
)engine=innodb;
(2) insert into user(name) values('shenjian');
(3) insert into user(id, name) values(10,'shenjian');
(4) insert into user(name) values('shenjian');
相关文章:
《索引,一文搞定》